/**
 * Created by apple on 2017/10/9.
 */
var DBFactory = require('../db/mysql_db');
var async = require('async');
var userAgentDao = module.exports;
var Config = require('../config');
var vipDao = require('../dao/vip.js');
var UserDao = require('./users');
var GlobalVar = require('../global_var');
var UserFreezeMoneyDao = require('../dao/user_freeze_money');
var request = require('request');
var WXBizDataCrypt = require('../util/WXBizDataCrypt');

//服务商、运营中心、总部模式    CONFIG.FWS_SELF_RECOMMAND = false; 推荐人不为自己，设空。  CONFIG.FWS_SELF_RECOMMAND = true，推荐人为自己
//需求说明，已是前置代理，或已有一个前置代理的 账号不能升级（//如果服务商是挂在总部账号底下的，可以修改）
//一、用户->运营商：1、给自己增加firstVip，等级+=户口数
//               2、改变自己的推荐人、间接推荐人、区代为自己
//               3、改变一级下线的间接推荐人为自己
//               4、改变所有下线的区代为自己(递归调用，人可能很多)
//二、用户->市代：  1、给自己增加firstVip，等级+=户口数
//               2、改变自己的推荐人、间接推荐人、区代、市代为自己
//               3、改变一级下线的间接推荐人为自己
//               4、改变所有下线的区代、市代为自己(递归调用，人可能很多)
//三、用户->省代：  1、给自己增加firstVip，等级+=户口数
//               2、改变自己的推荐人、间接推荐人、区代、市代、省代为自己
//               3、改变一级下线的间接推荐人为自己
//               4、改变所有下线的区代、市代、省代为自己(递归调用，人可能很多)
userAgentDao.setPreAgentLvl = function (data,cb) {
    if(!data) return cb(new Error(500));
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        async.waterfall([

            function (callback) {
                connection.beginTransaction(function (err) {
                    callback(err);
                });
            },

            //1、先查询该用户的代理信息
            function (callback) {

                connection.query("SELECT * FROM user_app_new_agent WHERE uid = ? AND app_sid = ? ", [data.uid,data.app_sid], function (err, result) {
                    if (err) {
                        callback('设置前置代理时查询用户代理表失败: ' + err);
                        return;
                    }

                    if(!result || result.length <= 0) return callback('该用户不存在');

                    data.agentInfo = result[0];
                    data.pre_agent_lvl = parseInt(data.pre_agent_lvl);
                    data.agentInfo.pre_agent_lvl = parseInt(data.agentInfo.pre_agent_lvl);

                    if (data.agentInfo.pre_agent_lvl == data.pre_agent_lvl) return callback('新等级不能等于旧等级！');

                    data.agentInfo.agent_1 = data.agentInfo.agent_1 ? data.agentInfo.agent_1 : null;
                    data.agentInfo.agent_2 = data.agentInfo.agent_2 ? data.agentInfo.agent_2 : null;
                    data.agentInfo.agent_3 = data.agentInfo.agent_3 ? data.agentInfo.agent_3 : null;
                    data.agentInfo.agent_4 = data.agentInfo.agent_4 ? data.agentInfo.agent_4 : null;
                    data.agentInfo.agent_5 = data.agentInfo.agent_5 ? data.agentInfo.agent_5 : null;
                    data.agentInfo.agent_6 = data.agentInfo.agent_6 ? data.agentInfo.agent_6 : null;

                    if(data.agentInfo.pre_agent_lvl > 0) {
                        if((data.pre_agent_lvl == 0 || data.agentInfo.pre_agent_lvl < data.pre_agent_lvl)){
                            if(data.onlyUpgrade != undefined && data.onlyUpgrade == 'true') {
                                callback('该用户已有更高的级别！');
                                return;
                            }
                            data.isReduceLvl = true;
                            if(data.pre_agent_lvl == 0){
                                data.setFromAgentToUser = true;
                            }
                        }
                    }
                    else{
                        data.setFromUserToAgent = true;
                    }
                    if(data.agentInfo.pre_agent_lvl){
                        data.myDirectAgent = data.agentInfo['agent_' + (data.agentInfo.pre_agent_lvl - 1)];
                    }
                    else{
                        data.myDirectAgent = data.agentInfo['agent_6'];
                    }

                    // if(!Config.ALLOW_UPGRADE_AGENT){
                    //     // 如果服务商是挂在总部账号底下的，可以修改
                    //     if (data.agentInfo.pre_agent_lvl > 0) return callback('已有身份，无法修改');
                    //
                    //     if (data.agentInfo.agent_4 && data.agentInfo.agent_4 != Config.USUZ_COMPANY_ACCOUNT) {
                    //         return callback('已有服务商，无法修改');
                    //     }
                    // }
                    callback(null,null);

                });

            },

            //2、设置自己的代理 //升级、降级修改
            function (res,callback) {
                var account = data.agentInfo.account;
                var sql = 'UPDATE user_app_new_agent SET recommender = ?,recommender2 = ?';
                var value = [];

                //降级操作，从 原等级 到 新等级的前一级 都改为 原等级的前一级 ； 新等级改为自己，其他不变
                if(data.isReduceLvl){
                    if(data.setFromAgentToUser){   // 降为普通用户，推荐人需修改为上级代理。
                        value.push(data.myDirectAgent, data.myDirectAgent);
                    }
                    else{
                        value.push(account, account);
                    }
                    var agentIdxSetS = data.agentInfo.pre_agent_lvl;
                    var agentIdxSetE = data.pre_agent_lvl ? (data.pre_agent_lvl - 1) : Config.MAX_AGENT_LEVEL;
                    for(var i = agentIdxSetS; i <= agentIdxSetE; ++i){
                        sql += ',agent_' + i + ' = ? ';
                        data.agentInfo['agent_' + i] = data.myDirectAgent;
                        value.push(data.myDirectAgent);
                    }
                    sql += ',agent_' + data.pre_agent_lvl + ' = ? ';
                    data.agentInfo['agent_' + data.pre_agent_lvl] = account;
                    value.push(account);
                }
                //升级操作 ,从原等级到新等级都改为自己，其他不变。
                else {
                    value.push(account, account);

                    var oldLvl = data.agentInfo.pre_agent_lvl ? data.agentInfo.pre_agent_lvl : Config.MAX_AGENT_LEVEL;//无等级的从最大值开始改；
                    for(var j = oldLvl; j >= data.pre_agent_lvl; j--){
                        sql += ',agent_' + j + ' = ? ';
                        data.agentInfo['agent_' + j] = account;
                        value.push(account);
                    }
                }

                sql += ', pre_agent_lvl = ? WHERE uid = ? AND app_sid = ? ';
                value.push(data.pre_agent_lvl, data.uid, data.app_sid);
                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback(err);
                        console.log('设置前置代理时修改用户代理表失败: ' + err);
                        return;
                    }

                    if(result.affectedRows > 0){
                        callback(null,null);
                        return;
                    }
                    callback('设置前置代理时修改用户代理表失败');

                });
            },

            //3、普通用户设置成代理，加 firstVip 或者 jihuo 的VIP权限
            function (res,callback) {
                if(!data.setFromUserToAgent) return callback(null,null);

                // if(!data.person_num) return callback(null,null);

                var param = {
                    app_key: data.app_sid,
                    account: data.agentInfo.account,
                    uid: data.uid,
                    person_num: data.person_num, //加的户口数
                    vip_sid: data.pre_agent_lvl == 6 ? 'firstVip' : 'jihuo',
                }
                vipDao.setUSUZFirstVip(param,function (err,result) {
                    if(err){
                        callback(err);
                        console.log('设置前置代理时修改用户vip加户口失败' + err);
                        return;
                    }
                    callback(null,null);
                })

            },


            //2、设置前就已经是代理了，只需要根据代理字段来查找所有团队成员。
            // 调整团队成员的新代理，可适用降级和设置成普通用户。
            function (res,callback) {
                if(!data.agentInfo.pre_agent_lvl){
                    callback(null, null);
                    return;
                }
                var sql = 'UPDATE user_app_new_agent SET ';
                var value = [];

                if(data.isReduceLvl){
                    var agentIdxSetS = data.agentInfo.pre_agent_lvl;
                    var agentIdxSetE = data.pre_agent_lvl ? (data.pre_agent_lvl - 1) : Config.MAX_AGENT_LEVEL;
                    for(var i = agentIdxSetS ; i <= agentIdxSetE; ++i){
                        var agName = 'agent_' + i;
                        sql += '' + agName + ' = if(' + agName + '=?, ?, ' + agName + ')';
                        if(i != agentIdxSetE) sql += ',';
                        value.push(data.agentInfo.account, data.agentInfo[agName]);
                    }
                }
                else{
                    var agentIdxSetS = data.pre_agent_lvl ? (data.pre_agent_lvl - 1) : Config.MAX_AGENT_LEVEL;
                    var agentIdxSetE = data.agentInfo.pre_agent_lvl;
                    for(var i = agentIdxSetS ; i <= agentIdxSetE; ++i){
                        var agName = 'agent_' + i;
                        sql += '' + agName + ' = ?';
                        if(i != agentIdxSetE) sql += ',';
                        value.push(data.agentInfo[agName]);
                    }
                }
                if(data.setFromUserToAgent || data.setFromAgentToUser){
                    sql += ', recommender2 = ?';
                    value.push(data.agentInfo.agent_6);
                }
                sql += ' WHERE app_sid = ? AND agent_' + (data.agentInfo.pre_agent_lvl) + ' = ' + data.agentInfo.account;
                value.push(data.app_sid, data.agentInfo.account);

                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback(err);
                        console.log(err);
                        console.log('设置前置代理时修改用户代理表失败: ' + err);
                        return;
                    }

                    callback(null,null);
                });
            },

            // 设置前不是代理，需要通过推荐人来查找团队成员。
            // 找出一级下线，然后改变一级下线的间接推荐人为自己。下个流程会递归调用来设置
            function (res, callback) {
                if(data.agentInfo.pre_agent_lvl){
                    callback(null, null);
                    return;
                }

                //查出一级下线
                var sql = 'SELECT account FROM user_app_new_agent WHERE recommender = ? AND app_sid = ? AND account <> ? ';
                var value = [data.agentInfo.account,data.app_sid,data.agentInfo.account];
                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback('设置前置代理时查询下级用户失败: ' + err);
                        console.log(err);
                        return;
                    }

                    if(!result || result.length <= 0){//没有下级，直接接口返回
                        callback(null,null);
                        return;
                    }

                    var firstLowAccount = [];
                    result.forEach(function (accountInfo) {
                        firstLowAccount.push(accountInfo.account);
                    });
                    var lowResult = result;
                    if (data.agentInfo.pre_agent_lvl){//如果已经是代理的，不用设置一级下线的间接推荐人为自己；
                        callback(null,lowResult);
                        return;
                    }
                    var upSql = 'UPDATE user_app_new_agent set recommender2 = ? WHERE account in ? AND app_sid = ? AND account <> ? ';
                    connection.query(upSql, [data.agentInfo.account, [firstLowAccount], data.app_sid, data.agentInfo.account], function (err, result) {
                        if (err) {
                            callback('设置前置代理时修改用户代理表失败: ' + err);
                            console.log(err);
                            return;
                        }
                        callback(null,lowResult);
                    });
                });
            },

            // 设置前不是代理，需要通过推荐人来查找团队成员。
            // 找出一级下线，然后改变一级下线的间接推荐人为自己。然后递归调用
            //5、设置所有下级的区代、市代(如有需要)、省代(如有需要)
            function (lowResult,callback) {
                if(data.agentInfo.pre_agent_lvl){
                    callback(null, null);
                    return;
                }

                if(!lowResult){
                    callback(null,'设置成功');
                    return;
                }

                // data.lowAgentAccounts = [];
                var lowAccounts = [];
                lowResult.forEach(function (userInfo) {
                    lowAccounts.push(userInfo.account);
                });
                data.allLowAcc = {};
                data.allLowAcc[data.account] = 1;
                data.unReachBottomAcc = lowAccounts;
                data.toUpdateLowAcc = lowAccounts;
                data.allLowAccSize = 0;
                lowResult.forEach(function (t) { data.allLowAcc[t] = 1; });

                UpdatePreAgentOfLowAcc(data, connection, callback);

                // recursiveSetLowUserPreAgent(data,lowResult,0,connection,function (err) {
                //
                //     if(err) return callback(err);
                //
                //     //把查出来的下级用户的市代，省代（有的话）修改
                //     var sql = 'UPDATE user_app_new_agent SET agent_4 = ? ';
                //     var value = [data.agentInfo.account];
                //     if(data.pre_agent_lvl > 1){//如果是市代，下级用户的省代也要改
                //         sql += ', agent_3 = ? ';
                //         value.push(data.agentInfo.account);
                //     }
                //     if(data.pre_agent_lvl > 2){//如果是省代，下级用户的省代也要改
                //         sql += ', agent_2 = ? ';
                //         value.push(data.agentInfo.account);
                //     }
                //     sql += 'WHERE account IN ? AND app_sid = ? ';
                //     value.push([data.lowAgentAccounts],data.app_sid);
                //
                //     connection.query(sql, value, function (err, result) {
                //         if (err) {
                //             callback('设置前置代理时修改下级用户失败: ' + err);
                //             console.log(err);
                //             return;
                //         }
                //
                //         if(result.affectedRows > 0){
                //             callback(null,'设置成功');
                //             return;
                //         }
                //         callback('设置失败');
                //
                //     });
                //
                //
                // });

            },


        ], function (err, res) {
            if (err) {
                connection.rollback(function () {
                    connection.release();
                    cb(err);
                });
            } else {
                connection.commit(function (e) {
                    if (e) {
                        connection.rollback(function () {
                            connection.release();
                            cb(e);
                        });
                    } else {
                        connection.release();
                        cb(null, res);
                        // if (data.agentInfo.pre_agent_lvl == 0){//用户升级加盟商附送一年天使
                        //     giveXQKSVip(data,function () {});
                        // }
                        //设置成功，再后台去设置减少上级的团队人数，不一定成功没关系，减少他这支的
                        //deleteTeamNum(data.agentInfo.recommender,data.agentInfo.app_sid,(data.agentInfo.team_num+1));
                    }
                });
            }
        });

    });
};

function giveXQKSVip(data,cb) {
    var param = {};
    param.app_time = new Date().getTime();
    param.app_sid = data.agentInfo.app_sid;
    param.app_key = data.agentInfo.app_sid;
    param.app_sign = getAppSign(param.app_key,GlobalVar.apps[param.app_key],param.app_time);
    param.vip_sid = 'xqkSVip';
    param.vip_type = 0;
    param.give_acc = data.agentInfo.account;
    param.duration = 365;
    param.lvl = 1;
    baseRequest('http://' + Config.USUZHost, '/user_vip/give_xqk_vip_by_srv', param, function(err, result){
        if(err) {
            console.log('give_xqk_vip_by_srv err:' + err);
        }
        cb(err,result);
        console.log(result);
    });
}

function UpdatePreAgentOfLowAcc(data, connection, callback){
    if(data.allLowAccSize > 1000000 ||(data.unReachBottomAcc.length <= 0 && data.toUpdateLowAcc.length <= 0)){
        callback(null);
        return;
    }

    if(data.unReachBottomAcc.length > 0 && data.toUpdateLowAcc.length < 100){
        var toSearchAccs = [];
        if(data.unReachBottomAcc.length > 100){
            toSearchAccs = data.unReachBottomAcc.splice(data.unReachBottomAcc.length - 100, 100);
        }
        else{
            toSearchAccs = data.unReachBottomAcc;
            data.unReachBottomAcc = [];
        }
        connection.query('SELECT account FROM user_app_new_agent WHERE recommender IN ? AND app_sid = ?', [[toSearchAccs],data.app_sid], function (err, result) {
            if (err) {
                callback('设置前置代理时查询下级用户失败: ' + err);
                console.log(err);
                return;
            }
            result.forEach(function (t) {
                if(!data.allLowAcc.hasOwnProperty(t.account)){
                    ++data.allLowAccSize;
                    data.unReachBottomAcc.push(t.account);
                    data.toUpdateLowAcc.push(t.account);
                }
            });
            UpdatePreAgentOfLowAcc(data, connection, callback);
        });
        return;
    }
    if(data.toUpdateLowAcc.length > 0){
        var toUpdateAccs = [];
        if(data.toUpdateLowAcc.length > 100){
            toUpdateAccs = data.toUpdateLowAcc.splice(data.toUpdateLowAcc.length - 100, 100);
        }
        else{
            toUpdateAccs = data.toUpdateLowAcc;
            data.toUpdateLowAcc = [];
        }

        var sql = 'UPDATE user_app_new_agent SET agent_1 = ?,agent_2 = ?,agent_3 = ?, agent_4 = ?,agent_5 = ?,agent_6 = ? ';
        var value = [data.agentInfo.agent_1, data.agentInfo.agent_2, data.agentInfo.agent_3,
            data.agentInfo.agent_4, data.agentInfo.agent_5, data.agentInfo.agent_6, data.app_sid];
        sql += ' WHERE app_sid = ? AND account in ?;';
        value.push([toUpdateAccs]);
        connection.query(sql, value, function (err, result) {
            if (err) {
                callback(err);
                console.log('设置前置代理时修改用户代理表失败: ' + err);
                return;
            }
            UpdatePreAgentOfLowAcc(data, connection, callback);
        });
        return;
    }
}
userAgentDao.getUserInfo = function (data, cb) {
    if (!data) return cb(new Error(500));

    var sql = 'SELECT uaa.*,u.ali_account,u.wx_account,u.money FROM user_app_new_agent uaa' +
        ' INNER JOIN users u ON uaa.uid = u.uid WHERE uaa.app_sid = ?';
    var sqlCount = 'SELECT count(*) AS count FROM user_app_new_agent WHERE app_sid = ?';
    var sqlValue = [];
    sqlValue.push(data.app_sid);
    if (data.pre_agent_lvl) {
        sql += ' AND uaa.pre_agent_lvl = ?';
        sqlCount += ' AND pre_agent_lvl = ?';
        sqlValue.push(data.pre_agent_lvl);
    }
    if(data.uids){
        sql += ' AND uaa.uid IN ('+data.uids+')';
        sqlCount += ' AND uid IN ('+data.uids+')';
    }

    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        async.waterfall([
                function (callback) {
                    if(data.pre_agent_lvl){
                        var pageSize = data.pageSize ? data.pageSize : 10;
                        var page = data.page ? data.page : 1;
                        //加页数限制 默认1页10条
                        sql += ' LIMIT ' + (page - 1) * pageSize + ',' + pageSize;
                    }
                    console.log(sql);
                    console.log(sqlValue);
                    connection.query(sql, sqlValue, function (err, result) {
                        callback(err, result);
                    });
                },
                function (users, callback) {
                    connection.query(sqlCount, sqlValue, function (err, result) {
                        if (err) {
                            callback(err);
                            console.log(err);
                            return;
                        }
                        if (result.length == 1) {
                            var userInfos = {};
                            userInfos.userInfos = users;
                            userInfos.totalCount = result[0].count;
                        }
                        callback(null, userInfos);
                    });
                }],
            function (err, res) {
                connection.release();
                if (err) {
                    cb(err);
                } else {
                    cb(null, res);
                }
            });
    });
}


//获取代理信息
userAgentDao.getUidByWxunionid= function(data, cb) {
    var sql = 'select uid from users where wx_unionid = ? ';
    DBFactory.getConnection(function(error, connection){
        if(error) {
            return cb(error);
        } else {
            connection.query(sql, [data.wx_unionid], function (err, result) {
                cb(err, result);
                connection.release();
            });
        }
    });
}
userAgentDao.getUserAgent = function (data,cb) {
    if(!data) return cb(new Error(500));

    var sql = data.uid == Config.USUZ_COMPANY_UID
        ? 'SELECT COUNT(*)-1 as total_num, COUNT(*)-1 as team_num,2 AS pre_agent_lvl FROM user_app_new_agent WHERE app_sid = ? '
        : 'SELECT uaa.pre_agent_lvl,uaa.team_num as total_num,uaa.team_num as team_num,uaa.is_show_up,uaa.agent_2 FROM user_app_new_agent uaa ' +
        'LEFT JOIN user_app_exinfo uae ON uaa.account = uae.account AND uaa.app_sid = uae.app_sid WHERE uaa.uid = ? AND uaa.app_sid = ? ';
    var value = data.uid == Config.USUZ_COMPANY_UID ? [data.app_sid] : [data.uid,data.app_sid];
    DBFactory.poolQuery(sql, value,
        function () {
            cb('no connection');
        }, function (err, result) {
            if (err) {
                cb(err);
                console.log(err);
                return;
            }
            var agentInfo = (result && result[0]) ? result[0] : {};
            if (result && result[0]){
                agentInfo[0] = {
                    'pre_agent_lvl': result[0].pre_agent_lvl,
                }
                agentInfo.is_show_up = 1;
                if (agentInfo.agent_2 == Config.JNTB_ACCOUNT) agentInfo.is_show_up = 1;
            }
            cb(null,agentInfo);
        });

}

//设置团队人数： 省代的团队人数实际上是自己单独发展的团队人数+市代们的团队人数；市代的团队人数就是自己发展的团队人数
//设置的时候的逻辑：
//循环设置自己的上级推荐人的团队人数，
// 1、查推荐人
// 2、判断没有推荐人时跳出；
// 3、判断推荐人是不是自己，如果是自己，说明是市代或省代，跳出的同时再设置下他的省代（如果有的话）
// 4、修改推荐人的团队人数
userAgentDao.addTeamNum = function (data) {

    if(!data.account || !data.app_sid) return;
    recursiveUpdateTeamNum(data.account,data.app_sid,0,0,1);
    addSubCount(data,false);
}

userAgentDao.addRealSubNum = function (data) {
    if(!data.account || !data.app_sid) return;
    recursiveUpdateTeamNum(data.account,data.app_sid,0,0,1,true);
}

function deleteTeamNum(account,app_sid,num) {
    updateAgentTeamNumByAccount([account],app_sid,1,num,function (err) {
        if(err) return;
        recursiveUpdateTeamNum(account,app_sid,0,1,num);
    });
}

function deleteRealSub(account,app_sid,num) {
    updateAgentRealSubByAccount([account],app_sid,1,num,function (err) {
        if(err) return;
        recursiveUpdateTeamNum(account,app_sid,0,1,num,true);
    });
}

function addTeamNum(account,app_sid,num) {
    updateAgentTeamNumByAccount([account],app_sid,0,num,function (err) {
        if(err) return;
        recursiveUpdateTeamNum(account,app_sid,0,0,num);
    });
}

function addRealSub(account,app_sid,num) {
    updateAgentRealSubByAccount([account],app_sid,0,num,function (err) {
        if(err) return;
        recursiveUpdateTeamNum(account,app_sid,0,0,num,true);
    });
}

//sign 0增 1减
function recursiveUpdateTeamNum(account,app_sid,index,sign,num,isReal) {

    // if(index > 100){//防止出现过度循环
    //     return;
    // }
    var sql = "SELECT * FROM user_app_new_agent WHERE account = ? AND app_sid = ? ";

    DBFactory.getConnection(function (error, connection) {
        if (error) {
            return;
        }
        //先查找推荐人及他的等级，如果没有或者推荐人是自己，说明已经到最顶级了，结束。
        connection.query(sql, [account,app_sid], function (err, result) {
            if (err) {
                console.log('设置推荐人上级的团队人数查询表失败');
                console.log(err);
                connection.release();
                return;
            }

            if(!result || result.length <= 0){//没有推荐人，已经到最顶级了
                connection.release();
                return;
            }

            var recommenderInfo = result[0];


            //推荐人是自己的，有可能是区代、省代或市代，结束循环； 如果是区代，市代且有省代，再设置下他的省代的人数
            if(recommenderInfo.recommender == account || recommenderInfo.pre_agent_lvl > 0) {
                var accounts = [];
                var i = recommenderInfo.pre_agent_lvl > 0 ? recommenderInfo.pre_agent_lvl : 6;
                for(;i >= 1; --i){
                    var agentAcc = recommenderInfo['agent_' + i];
                    if(agentAcc && agentAcc != account) accounts.push(agentAcc);
                }
                //
                // if(recommenderInfo.pre_agent_lvl == 1){//如果是区代，再设置下他的市代、省代的团队人数
                //     if(recommenderInfo.agent_3) accounts.push(recommenderInfo.agent_3);
                //     if(recommenderInfo.agent_2) accounts.push(recommenderInfo.agent_2);
                // }
                // if(recommenderInfo.pre_agent_lvl == 2){//如果是市代，再设置下他的省代的团队人数
                //     if(recommenderInfo.agent_2) accounts.push(recommenderInfo.agent_2);
                // }
                connection.release();
                if(accounts.length > 0) updateAgentTeamNumByAccount(accounts,app_sid,sign,num,function () {});
                return;
            }

            var sql;
            if (isReal) {
                sql = sign == 0 ? 'UPDATE user_app_exInfo SET r_sub_total = r_sub_total + ? WHERE account = ? AND app_sid = ? ' :
                    'UPDATE user_app_exInfo SET r_sub_total = r_sub_total - ? WHERE account = ? AND app_sid = ? ';
            }else {
                sql = sign == 0 ? 'UPDATE user_app_new_agent SET team_num = team_num + ? WHERE account = ? AND app_sid = ? ' :
                    'UPDATE user_app_new_agent SET team_num = team_num - ? WHERE account = ? AND app_sid = ? ';
            }

            //如果有推荐人，则添加下团队人数，并继续找上一级
            connection.query(sql, [num,recommenderInfo.recommender,app_sid], function (err, result) {
                if (err) {
                    console.log('设置推荐人上级的团队人数失败');
                    console.log(err);
                    connection.release();
                    return;
                }

                if(result.affectedRows <= 0){
                    connection.release();
                    return;
                }

                connection.release();
                recursiveUpdateTeamNum(recommenderInfo.recommender,app_sid,index+1,sign,num,isReal);

            });
        });
    });
}

//sign 0增 1减
function updateAgentTeamNumByAccount(accounts,app_sid,sign,num,cb) {
    var sql = sign == 0 ? 'UPDATE user_app_new_agent SET team_num = team_num + ? WHERE app_sid = ? AND account IN ? ' :
        'UPDATE user_app_new_agent SET team_num = team_num - ? WHERE app_sid = ? AND account IN ? ';
    DBFactory.poolQuery(sql, [num,app_sid,[accounts]],
        function () {
            cb('设置团队人数连接数据库出错')
            return;
        }, function (err, result) {
            if (err) {
                cb(err);
                console.log('设置代理团队人数出错');
                console.log(err);
                return;
            }
            cb(null);
        });
}

function updateAgentRealSubByAccount(accounts,app_sid,sign,num,cb) {
    var sql = sign == 0 ? 'UPDATE user_app_exinfo SET r_sub_total = r_sub_total + ? WHERE app_sid = ? AND account IN ? ' :
        'UPDATE user_app_exinfo SET r_sub_total = r_sub_total - ? WHERE app_sid = ? AND account IN ? ';
    DBFactory.poolQuery(sql, [num,app_sid,[accounts]],
        function () {
            cb('设置团队人数连接数据库出错')
            return;
        }, function (err, result) {
            if (err) {
                cb(err);
                console.log('设置代理团队人数出错');
                console.log(err);
                return;
            }
            cb(null);
        });
}

userAgentDao.addUserUUID = function (data,cb) {
    if(!data) return cb(new Error(500));


    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        connection.query('SELECT uid FROM user_app_new_agent WHERE uuid = ? AND app_sid = ? ', [data.uuid,data.app_sid], function (err, result) {
            if (err) {
                cb(err);
                connection.release();
                return;
            }
            if (result && result[0] && result[0].uid){
                cb(null,'已添加的设备');
                connection.release();
                return;
            }
            connection.query('SELECT uuid FROM user_app_new_agent WHERE uid = ? AND app_sid = ? ',[data.uid,data.app_sid],function (err,result) {
                if (err) {
                    cb(err);
                    connection.release();
                    return;
                }
                if (result && result[0] && result[0].uuid){
                    cb(null,'已添加的用户');
                    connection.release();
                    return;
                }
                connection.query('UPDATE user_app_new_agent SET uuid = ? WHERE uid = ? AND app_sid = ? ', [data.uuid,data.uid,data.app_sid], function (err, result) {
                    if (err) {
                        cb(err);
                        connection.release();
                        return;
                    }
                    if(result.affectedRows > 0) {
                        cb(null, '设置成功');
                        userAgentDao.addRealSub(data,'uuid');
                        unfreezeMoneyUUID(data); //活动期间临时增加
                        unfreezeMoneyNew(data);
                    }else {
                        cb('设置失败');
                    }
                    connection.release();
                });
            });
        });
    });

}

function unfreezeMoneyUUID(data) {
    UserFreezeMoneyDao.unfreezeMoney({uid:data.uid,app_sid:data.app_sid,unfreeze_sid:'login'},function (err,result) {
        if(err) console.log('' +  data.uid + ' 登录解锁失败Err ' + err);
    });
}

//增加有效下级. 1、判断是否有效，2、有效的用户进行操作
//1、data={uuid,uid,app_sid}   2、condition_key: uuid、ali_user_id、all。
//  uuid：查有无ali_user_id确认是否有效； ali_user_id：查有无uuid确认是否有效；  all： 查有无uuid、ali_user_id
userAgentDao.addRealSub = function(data,condition_key) {

    var sql1 = '';
    var sql2 = '';
    var key1 = '';
    var key2 = '';
    switch (condition_key) {
        case 'uuid' : {
            sql1 = 'SELECT ali_user_id FROM user_app_exinfo WHERE uid = ? and app_sid = ? ';
            key1 = 'ali_user_id';
            break;
        }
        case 'ali_user_id' : {
            sql1 = 'SELECT uuid FROM user_app_new_agent WHERE uid = ? AND app_sid = ? ';
            key1 = 'uuid';
            break;
        }
        case 'all' : {
            sql1 = 'SELECT ali_user_id FROM user_app_exinfo WHERE uid = ? and app_sid = ? ';
            sql2 = 'SELECT uuid FROM user_app_new_agent WHERE uid = ? AND app_sid = ? ';
            key1 = 'ali_user_id';
            key2 = 'uuid';
            break;
        }
    }
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            console.log(error);
            return;
        }
        connection.query(sql1, [data.uid,data.app_sid], function (err, result) {
            if (err) {
                console.log('userAgentDao-addRealSub-ERR:' + sql1);
                connection.release();
                return;
            }
            if (!result || !result[0] || !result[0][key1]){ //没查询到，可以直接返回
                console.log('没查询到，可以直接返回');
                connection.release();
                return;
            }
            if (key2 == '') {//只查询uuid或ali_user_id，且有另一个，说明是有效用户
                connection.release();
                realSubHandle(data); //是有效用户后的操作
                return;
            }
            connection.query(sql2, [data.uid,data.app_sid], function (err, result) {
                if (err) {
                    console.log('userAgentDao-addRealSub-ERR:' + sql2);
                    connection.release();
                    return;
                }
                connection.release();
                if (result && result[0] && result[0][key2]){ //ali_user_id，uuid都有，说明是有效用户
                    realSubHandle(data); //是有效用户后的操作
                }
            });

        });
    });

};

/*
 有效推荐人后的操作,可异步执行
 1、增加一级、二级有效下线人数
 2、增加总有效下线人数
 3、增加活动余额
* */
function realSubHandle(data) {
    addSubCount(data,true);              //增加推荐人的一级有效下线人数； 二级推荐人的二级有效下线人数。
    userAgentDao.addRealSubNum(data);    //增加推荐人的总有效下线人数
    // addUSUZNewPromotion(data);
}

/*
   解锁上级的冻结金额
   1、查询上级的uid，
   2、有上级的话，尝试解锁对应金额
*/
function unfreezeMoneyNew(data){
    if(data.app_sid != 'usuz') return;
    findRecUid(data,function (err,result) {
        if(err) {
            console.log('' + err);
            return;
        }
        if (result && result[0] && result[0].recommenderUid) {
            var param = {
                uid:result[0].recommenderUid,
                app_sid:data.app_sid,
                unfreeze_sid:'lx_' + data.uid,
            };
            UserFreezeMoneyDao.unfreezeMoney(param,function (err,result) {
                if(err){
                    console.log('' + data.uid + ' 解锁佣金失败' + err);
                }
            });
        }
    });
}

function findRecUid(data,cb) {
    var sql = 'SELECT uaa.uid,u.uid as recommenderUid FROM user_app_new_agent uaa ' +
        'LEFT JOIN users u ON uaa.recommender = u.account WHERE app_sid = ? AND uaa.uid = ? ';
    DBFactory.poolQuery(sql, [data.app_sid,data.uid],
        function () {
            cb('no collection');
            return;
        }, function (err, result) {
            if (err) {
                cb(err);
                return;
            }
            cb(null,result);
        });
}

/*
  增加推荐人的有效人数，如果达标升级微淘客，仅usuz
  1、查找推荐人信息,如果自己没有推荐人，且加入时间不在2018年3月8日后的，不算新用户
  2、存在推荐人的发送至usuz操作
 */
function addUSUZNewPromotion(data){
    if (data.app_sid != 'usuz') return;
    //1、
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            console.log('addUSUZNewPromotion CONNECTION ERR');
            return;
        }
        var sql = "SELECT recommender,created_time FROM user_app_new_agent WHERE uid = ? AND app_sid = 'usuz' ";
        connection.query(sql, [data.uid], function (err, result) {
            if (err) {
                console.log('addUSUZNewPromotion ');
                connection.release();
                return;
            }
            var userInfo = result && result[0] ? result[0] : null;
            if (!userInfo || !userInfo.recommender || simpleDateFormat(userInfo.created_time) < '2018-03-08 08:59:59') {
                connection.release();
                console.log('不符合拉新条件');
                return;
            }
            var sql2 = "SELECT uid,account FROM user_app_new_agent WHERE account = ? AND app_sid = 'usuz' ";
            connection.query(sql2, [userInfo.recommender], function (err, result) {
                if (err) {
                    console.log('addUSUZNewPromotion ');
                    connection.release();
                    return;
                }
                connection.release();
                //2、
                if (result && result[0] && result[0].uid) {//如果有推荐人，去修改推荐人的信息
                    sendAddPromotionUserToUSUZ({uid:result[0].uid,account:result[0].account});
                }
            });
        });
    });
}

function sendAddPromotionUserToUSUZ(data) {
    var param = {};
    param.app_time = new Date().getTime();
    param.uid = data.uid;
    param.account = data.account;
    param.app_key = 'usuz';
    param.app_sign = getAppSign(param.app_key,GlobalVar.apps[param.app_key],param.app_time);
    baseRequest('http://' + Config.USUZHost, '/new_promotion/add_user', param, function(err, data){
        if(err) {
            console.log('addRealUserAndMoney err:' + err);
        }
        console.log(data);
    });
}


//data:{uid,app_sid}
function addSubCount(data,isReal) {
    var sql = 'SELECT recommender,recommender2,pre_agent_lvl FROM user_app_new_agent WHERE uid = ? AND app_sid = ? ';
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            console.log(error);
            return;
        }
        connection.query(sql, [data.uid,data.app_sid], function (err, result) {
            if (err) {
                console.log('增加用户数时SELECT ERR' + err);
                connection.release();
                return;
            }
            if (!result || !result[0]){
                connection.release();
                return;
            }
            var recommenderInfo = result[0];
            var sub_field_name = isReal ? 'real_sub_ea' : 'sub_ea';
            updateCountWithField(recommenderInfo.recommender,data.app_sid,sub_field_name,connection,function (err) {
                if(err || recommenderInfo.pre_agent_lvl > 0) {//如果是前置代理，间接推荐人也是前置代理，不用加。
                    connection.release();
                    return;
                }
                // if (isReal) updateUSUZVipForFree(data);   //增加有效用户时，多加一个免费升级微淘客，方法里会判断是否达到10个有效用户
                if (recommenderInfo.recommender == recommenderInfo.recommender2) {
                    connection.release();
                    return;
                }
                var isub_field_name = isReal ? 'real_isub_ea' : 'isub_ea';
                updateCountWithField(recommenderInfo.recommender2,data.app_sid,isub_field_name,connection,function (err) {
                    connection.release();
                    return;
                });
            })
        });
    });
}

function updateCountWithField(account,app_sid,field_name,connection,cb) {
    if (!account) {
        connection.release();
        return;
    }
    var sql = 'UPDATE user_app_exInfo SET ' + field_name + '  = ' + field_name + ' + 1 ' +
        ' WHERE account = ? AND app_sid = ? ';
    connection.query(sql,[account,app_sid],function (err,result) {
        if (err) {
            console.log('增加用户数时UPDATE ERR' + err);
            cb(err);
            return;
        }
        cb(null);
    });
}

userAgentDao.findLoginSubNum = function (data,cb) {
    if(!data) return cb(new Error(500));
    var sql = 'SELECT SUM(!ISNULL(uuid)) as num FROM user_app_new_agent WHERE recommender = ? AND account <> ? AND app_sid = ? ';
    DBFactory.poolQuery(sql, [data.account,data.account,data.app_sid],
        function () {
            cb('no collection');
        }, function (err, result) {
            if (err) {
                cb(err);
                return;
            }
            if(result && result[0]){
                cb(null,{num:result[0].num});
                return;
            }
            cb('查询有误');
        });
}


function updateUSUZVipForFree(data) {
    if(!data) return;
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            console.log('updateUSUZVipForFree Connection Err');
            return;
        }
        //先查该用户的推荐人，没有的话直接返还
        connection.query('SELECT uaa.recommender,u.uid FROM user_app_new_agent uaa LEFT JOIN users u ON uaa.recommender = u.account WHERE uaa.uid = ? AND uaa.app_sid = ? ', [data.uid,data.app_sid], function (err, result) {
            if (err) {
                console.log('user_agent/updateUSUZVipForFree Err: SELECT uaa.recommender,u.uid FROM user_app_new_agent uaa LEFT JOIN users u ON uaa.recommender = u.account WHERE uaa.uid = ? AND uaa.app_sid = ? ');
                console.log(err);
                connection.release();
                return;
            }
            if (!result || !result[0] || !result[0].recommender){
                console.log('该用户没有推荐人,不操作');
                connection.release();
                return;
            }
            //再查该用户推荐人的有效用户数，够10个的话设置免费会员
            const recommender = result[0].recommender;
            const uid = result[0].uid;
            var sql = 'SELECT real_sub_ea FROM user_app_exinfo WHERE uid = ? AND app_sid = ? ';
            connection.query(sql,[uid,data.app_sid],function (err,result) {
                if (err) {
                    console.log('user_agent/updateUSUZVipForFree Err:');
                    console.log(err);
                    connection.release();
                    return;
                }
                connection.release();
                if(result && result[0] && result[0].real_sub_ea >= 10){
                    //替换成推荐人的信息
                    data.vip_sid = 'jihuo';
                    data.uid = uid;
                    data.account = recommender;
                    vipDao.setUSUZFirstVip(data,function (err,result) {
                        if(err) console.log(err);
                    });
                }
            });
        });
    });
}



userAgentDao.setSubWithNum = function (data,cb) {
    if (!data) return cb(new Error(500));
    if(!data.num) return cb('无人数');
    if(!data.uid) return cb('无用户标识');
    if(!data.app_sid) return cb('无产品标识');

    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        //先查该用户是普通用户、区代、市代、还是省代； 普通用户只设置推荐人，区代多设置直、间、区；
        connection.query('SELECT account,pre_agent_lvl FROM user_app_new_agent  WHERE uid = ? AND app_sid = ? ', [data.uid,data.app_sid], function (err, result) {
            if (err) {
                console.log('后台分配下级失败')
                console.log(err);
                connection.release();
                return;
            }
            if (!result || !result[0]){
                console.log('后台分配下级时找不到该用户');
                connection.release();
                return;
            }
            //根据用户等级设定推荐人
            var sql = "UPDATE user_app_new_agent SET recommender = ? ";
            var value = [result[0].account];

            sql += "WHERE account IN " +
                "( " +
                "SELECT a.account FROM " +
                "(" +
                "   SELECT uaa.account FROM user_app_new_agent uaa " +
                "   LEFT JOIN user_vip uv ON uaa.uid = uv.uid AND uv.app_sid = 'usuz' " +
                "   WHERE ISNULL(uaa.recommender) AND  NOT ISNULL(uv.vip_sid)  AND uaa.app_sid = 'usuz' GROUP BY uaa.uid LIMIT ? " +
                ") a" +
                ") ";
            value.push(data.num);

            connection.query(sql,value,function (err,result) {
                if (err) {
                    console.log(err);
                    connection.release();
                    return;
                }
                connection.release();
            });
        });
    });

}


//data里有自己的account 新推荐人的newRecommender,oldRecommender ：用户A要将推荐人换为R
//1、查出R,并查出他的推荐人R-R、间接推荐人R-R2、区、市、省
//2、换A的推荐人为R、间接推荐人R-R、3级推荐人R-R2、区、市、省代
//3、换B的间接推荐人为R、区、市、省
//4、换C、D、E....的区、市、省
userAgentDao.changeRecommender = function (data,cb) {
    if (!data) return cb(new Error(500));

    findNewRecommenderInfo(data.new_recommender,data.app_sid,function (err,recommenderInfo) {
        if (err) return cb(err);
        if(recommenderInfo.pre_agent_lvl > 0){
            cb('有前置代理，不可修改！');
            return;
        }
        //去操作更新的数据库
        updateNewRecommender(data.account,data.old_recommender,recommenderInfo,data.app_sid,cb);
    });

}

//1、查出R,并查出他的推荐人R-R、间接推荐人R-R2、区、市、省
function findNewRecommenderInfo(account,app_sid,cb) {
    if (!account) return cb('推荐人不存在');
    if (!app_sid) return cb('产品标识不为空');
    var sql = 'SELECT account,recommender,recommender2,agent_1, agent_2, agent_3, agent_4, agent_5, agent_6' +
        ' FROM user_app_new_agent WHERE account = ? AND app_sid = ? ';
    DBFactory.poolQuery(sql, [account,app_sid],
        function () {
            cb('findNewRecommenderInfo Err');
            console.log('findNewRecommenderInfo Err');
            return;
        }, function (err, result) {
            if (err) {
                console.log('findNewRecommenderInfo Err:' + err);
                cb(err);
                return;
            }
            if(result && result[0]){
                cb(null,result[0])
                return;
            }
            cb('推荐人不存在');
        });
}
//具体操作修改数据库的
function updateNewRecommender(account,oldRecommender,recommenderInfo,app_sid,cb) {

    var teamNum = 1;
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        async.waterfall([

            function (callback) {
                connection.beginTransaction(function (err) {
                    callback(err);
                });
            },

            //2、换A的推荐人为R、间接推荐人R-R、3级推荐人R-R2、区、市、省代
            function (callback) {

                var sql = 'UPDATE user_app_new_agent SET recommender = ?,recommender2 = ?,' +
                    'agent_1 = ?,agent_2 = ?,agent_3 = ?, agent_4 = ?,agent_5 = ?,agent_6 = ? ' +
                    'WHERE account = ? AND app_sid = ? ';
                var value = [recommenderInfo.account,recommenderInfo.recommender,
                    recommenderInfo.agent_1, recommenderInfo.agent_2,recommenderInfo.agent_3,
                    recommenderInfo.agent_4, recommenderInfo.agent_5,recommenderInfo.agent_6,
                    account,app_sid];
                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback('updateNewRecommender Err: ' + err);
                        return;
                    }
                    callback(null,null);

                });

            },
            //3、换B的间接推荐人为R、区、市、省
            //查出所有的一级下线并替换
            function (res, callback) {

                //查出一级下线
                var sql = 'SELECT account FROM user_app_new_agent WHERE recommender = ? AND app_sid = ? ';
                var value = [account,app_sid];

                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback(err);
                        console.log('修改推荐人时查询下级用户失败: ' + err);
                        return;
                    }

                    if(!result || result.length <= 0){//没有下级，直接接口返回
                        callback(null,null);
                        return;
                    }

                    var firstLowResult = result;

                    var firstLowAccount = [];
                    result.forEach(function (accountInfo) {
                        firstLowAccount.push(accountInfo.account);
                    });

                    var upSql = 'UPDATE user_app_new_agent set recommender2 = ?,agent_1 = ?,agent_2 = ?,agent_3 = ?, agent_4 = ?,agent_5 = ?,agent_6 = ?' +
                        ' WHERE app_sid = ? AND account IN  ? ';
                    var value = [recommenderInfo.account,recommenderInfo.agent_1, recommenderInfo.agent_2,recommenderInfo.agent_3,
                        recommenderInfo.agent_4, recommenderInfo.agent_5,recommenderInfo.agent_6,app_sid,[firstLowAccount]];
                    connection.query(upSql, value, function (err, result) {
                        if (err) {
                            callback('修改推荐人时修改用户代理表失败: ' + err);
                            console.log(err);
                            return;
                        }
                        callback(null,firstLowResult);
                    });


                });
            },

            //4、换C、D、E....的区、市、省
            function (lowResult,callback) {
                if (!lowResult) return callback(null,'设置成功');

                var data = {
                    lowAgentAccounts:[],
                    app_sid:app_sid
                };

                recursiveFindLowUser(data,lowResult,0,connection,function (err) {

                    if (err) return callback(err);

                    if (data.lowAgentAccounts.length <= 0) return callback(null,'设置成功');
                    //把查出来的下级用户的市代，省代（有的话）修改
                    var upSql = 'UPDATE user_app_new_agent set agent_1 = ?,agent_2 = ?,agent_3 = ?, agent_4 = ?,agent_5 = ?,agent_6 = ?' +
                        ' WHERE app_sid = ? AND account IN  ? ';
                    var value = [recommenderInfo.agent_1, recommenderInfo.agent_2,recommenderInfo.agent_3,
                        recommenderInfo.agent_4, recommenderInfo.agent_5,recommenderInfo.agent_6, app_sid, [data.lowAgentAccounts]];

                    teamNum += data.lowAgentAccounts.length;

                    connection.query(upSql, value, function (err, result) {
                        if (err) {
                            callback(err);
                            console.log('修改推荐人时修改下级用户失败: ' + err);
                            return;
                        }

                        if(result.affectedRows > 0){
                            callback(null,'设置成功');
                            return;
                        }
                        callback('设置失败');

                    });

                });
            }

        ], function (err, res) {
            if (err) {
                connection.rollback(function () {
                    connection.release();
                    cb(err);
                });
            } else {
                connection.commit(function (e) {
                    if (e) {
                        connection.rollback(function () {
                            connection.release();
                            cb(e);
                        });
                    } else {
                        connection.release();
                        cb(null, res);

                        //设置成功，减少原推荐人团队人数，增加新推荐人团队人数
                        // if (oldRecommender) deleteTeamNum(oldRecommender,app_sid,teamNum);
                        // addTeamNum(recommenderInfo.account,app_sid,teamNum);

                    }
                });
            }
        });

    });

}

//递归查询下级用户
//data = {app_sid:app_sid,lowAgentAccounts:lowAgentAccounts}
//
function recursiveFindLowUser(data,lowResult,index,connection,callback) {

    if(!lowResult || lowResult.length <= 0 || index>1000){
        callback(null);
        return;
    }
    var accounts = [];
    lowResult.forEach(function (userInfo) {
        accounts.push(userInfo.account);
        data.lowAgentAccounts.push(userInfo.account);
    });
    connection.query('SELECT account FROM user_app_new_agent WHERE app_sid = ? AND recommender IN ? ', [data.app_sid,[accounts]], function (err, result) {
        if (err) {
            callback('设置前置代理时查询下级用户失败: ' + err);
            console.log(err);
            return;
        }
        //递归调用查下级的下级
        recursiveFindLowUser(data,result,index+1,connection,callback);
    });
}

userAgentDao.getWXunionid = function(data, callback) {
    var getOpenid = 'https://api.weixin.qq.com/sns/oauth2/access_token?appid=' +
        Config.WXAuth[data.code_type].AppID + '&secret=' + Config.WXAuth[data.code_type].AppSecret +
        '&code='+ data.code +'&grant_type=authorization_code';
    request({url: getOpenid, method: 'GET'}, function (error, response, body) {
        if(error || !body) {
            console.log(error);
            return callback('获取openid失败！');
        } else {
            try {
                var openIdInfo = JSON.parse(body);
                if( !openIdInfo.access_token || !openIdInfo.openid) {
                    console.log(openIdInfo);
                    return callback('获取openid失败！');
                }
                var getWXid = 'https://api.weixin.qq.com/sns/userinfo?access_token='+ openIdInfo.access_token +
                    '&openid=' + openIdInfo.openid;
                request({url: getWXid, method: 'GET'}, function (error, response, body) {
                    if(error || !body) {
                        console.log(error);
                        return callback('获取微信id失败！');
                    } else {
                        try {
                            var wxInfo = JSON.parse(body);
                            if(!wxInfo.unionid) {
                                return callback('获取微信id失败！');
                            } else {
                                return callback(null, wxInfo);
                            }
                        } catch (e) {
                            return callback('获取微信id失败！');
                        }
                    }
                })
            } catch (e) {
                console.log(e);
                return callback('获取openid失败！');
            }
        }
    });
}
function getInfoByWX(data, connection, callback) {
    var sql = 'select uid, account from users where wx_unionid = ? ';
    connection.query(sql, [data.wx_unionid], function(err, result) {
        if(err) {
            callback(err);
        } else {
            if(result && result.length > 0 && result[0]) {
                data.uid = result[0].uid;
                data.account = result[0].account;
                callback(null, false);
            } else if(result.length == 0) {
                callback(null, true);
            }
        }
    });
}
function getInfoAndRecByWX(data, connection, callback) {
    var sql = 'SELECT u.uid, u.account, uaa.pre_agent_lvl FROM users u ' +
        ' LEFT JOIN user_app_new_agent uaa ON u.uid = uaa.uid WHERE u.wx_unionid = ? and uaa.app_sid = ? ';
    connection.query(sql, [data.wx_unionid,data.app_sid], function(err, result) {
        if(err) {
            callback(err);
        } else {
            var userInfo = result && result[0] ? result[0] : {};
            callback(null,{userInfo:userInfo});
        }
    });
}
function saveWXRecInfo(data, connection, callback) {
    var sql = 'select recommendUid from user_app_wx_rec where app_sid = ? and wx_unionid = ? ';
    connection.query(sql, [data.app_sid, data.wx_unionid], function(e, r) {
        if(e) {
            callback(e);
        } else {
            if(r.length > 0) {
                callback(null, {canBind:true,recommendUid:r[0].recommendUid});
            } else {
                var sql1 = 'insert into user_app_wx_rec set ? ';
                var info = {app_sid: data.app_sid, wx_unionid: data.wx_unionid, recommendUid: data.recommendUid};
                connection.query(sql1, info, function(err, result) {
                    if(err) {
                        callback(err);
                    } else {
                        callback(null, {canBind:true});
                    }
                });
            }
        }
    });
}
function isRegistApp(data, connection, callback) {
    var sql = 'select count(*) as count from users LEFT JOIN user_app_new_agent on users.uid = user_app_new_agent.uid ' +
        ' where users.wx_unionid = ? and app_sid = ? ';
    connection.query(sql, [data.wx_unionid, data.app_sid], function(err, result) {
        if(err) {
            callback(err);
        } else {
            if(result && result[0].count > 0 ) {
                hasRec(data, connection, callback);
            } else {
                noRegistApp(data, callback);
            }
        }
    });
}
function noRegistApp(data, callback) {
    UserDao.wxlogin(data, function(err, result) {
        if(err) {
            callback(err);
        } else {
            UserDao.updateRecommender(data, function (err, result) {
                if (err) {console.log(err);}
                callback(null, data);
            });
        }
    });
}
function hasRec(data, connection, callback) {
    var sql = 'select recommender from user_app_new_agent where uid = ? and app_sid = ?';
    connection.query(sql, [data.uid, data.app_sid], function(err, result) {
        if(err) {
            callback(err);
        } else {
            if(result && result[0] && result[0].recommender) {
                callback(null, data);
            } else {
                UserDao.updateRecommender(data, function (err, result) {
                    if (err) {
                        callback(err);
                        return;
                    }
                    callback(null, data);
                });
            }
        }
    });
}
userAgentDao.saveWxRecInfo = function(data, cb) {
    if(!data) return cb(new Error(500));
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
        } else {
            async.waterfall([
                    function(callback) {
                        getInfoByWX(data, connection, callback);
                    },
                    function(canSave, callback) {
                        if(canSave) {
                            saveWXRecInfo(data, connection, callback);
                        } else {
                            isRegistApp(data, connection, callback);
                        }
                    }
                ],
                function (DbErr, Info) {
                    connection.release();
                    if (DbErr) {
                        cb(DbErr);
                    } else {
                        cb(null, Info);
                    }
                }
            );
        }
    });
}

userAgentDao.getInfoByWX = function(data, cb) {
    if(!data) return cb(new Error(500));
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        getInfoAndRecByWX(data, connection, function (err,result) {
            if (err) {
                connection.release();
                cb(err);
                return;
            }
            var userInfo = result.userInfo;
            var canBind = true;
            if (userInfo && userInfo.uid) canBind = false;
            if (!canBind) {
                connection.release();
                cb(null,{canBind:canBind,userInfo:userInfo});
                return;
            }
            getRecommendUidByWX(data,connection,function (err1,result1) {
                connection.release();
                if (err1) {
                    cb(err1);
                    return;
                }
                var wxRecommendInfo = result1.wxRecommendInfo;
                if (wxRecommendInfo.recommendUid) userInfo.recommendUid = wxRecommendInfo.recommendUid;
                cb(null,{canBind:canBind,userInfo:userInfo});
            });
        });
    });
};

function getRecommendUidByWX(data,connection,cb) {
    var sql = 'SELECT recommendUid FROM user_app_wx_rec WHERE wx_unionid = ? ';
    connection.query(sql, [data.wx_unionid], function(err, result) {
        if(err) {
            cb(err);
        } else {
            var wxRecommendInfo = result && result[0] ? result[0] : {};
            cb(null,{wxRecommendInfo:wxRecommendInfo});
        }
    });
}

function wxGetUserInfo(data) {
    var pc = new WXBizDataCrypt(Config.WXAuth[data.code_type].AppID, data.sessionKey);
    var userInfo = pc.decryptData(data.encryptedData , data.iv);
    return userInfo;
}
userAgentDao.getMinProWXunionid = function(data, callback) {
    var getWxunionidUrl = 'https://api.weixin.qq.com/sns/jscode2session?appid=' +
        Config.WXAuth[data.code_type].AppID + '&secret=' + Config.WXAuth[data.code_type].AppSecret +
        '&js_code=' + data.code + '&grant_type=authorization_code';
    request({url: getWxunionidUrl, method: 'GET'}, function (error, response, body) {
        if(error || !body) {
            console.log(error);
            return callback('获取openid失败！');
        } else {
            try {
                var wxUnionidInfo = JSON.parse(body);
                if(!wxUnionidInfo.unionid) {
                    if(!data.encryptedData || !data.iv || !wxUnionidInfo.session_key) return callback({status: 405}); //获取不到unionid的情况
                    data.sessionKey = wxUnionidInfo.session_key;
                    var userInfo = wxGetUserInfo(data);
                    if(userInfo.unionId) {
                        wxUnionidInfo.unionid = userInfo.unionId;
                        return callback(null, wxUnionidInfo);
                    } else {
                        return callback('获取wx_unionId失败！');
                    }
                } else {
                    return callback(null, wxUnionidInfo);
                }
            } catch (e) {
                console.log(e);
                return callback('获取wxunionId失败！');
            }
        }
    });
}

userAgentDao.setPreAgentLvlTest = function (data,cb) {
    if(!data) return cb(new Error(500));
    DBFactory.getConnection(function (error, connection) {
        if (error) {
            cb(error);
            return;
        }
        async.waterfall([

            function (callback) {
                connection.beginTransaction(function (err) {
                    callback(err);
                });
            },

            //1、先查询该用户的代理信息
            function (callback) {

                connection.query("SELECT * FROM user_app_new_agent WHERE uid = ? AND app_sid = ? ", [data.uid,data.app_sid], function (err, result) {
                    if (err) {
                        callback('设置前置代理时查询用户代理表失败: ' + err);
                        return;
                    }

                    if(!result || result.length <= 0) return callback('该用户不存在');

                    data.agentInfo = result[0];
                    callback(null,null);

                });

            },

            //2、设置自己的代理 //升级、降级修改
            function (res,callback) {
                var account = data.agentInfo.account;
                var sql = 'UPDATE user_app_new_agent SET recommender = ?,recommender2 = ?';
                var value = [account,account];
                if (data.agentInfo.pre_agent_lvl == data.pre_agent_lvl) return callback('非法操作');
                if(data.agentInfo.pre_agent_lvl && data.agentInfo.pre_agent_lvl < data.pre_agent_lvl){//降级操作，从 原等级 到 新等级的前一级 都改为 原等级的前一级 ； 新等级改为自己，其他不变
                    var newAgent = data.agentInfo['agent_' + (data.agentInfo.pre_agent_lvl - 1)] ? data.agentInfo['agent_' + (data.agentInfo.pre_agent_lvl - 1)] : null;
                    for(var i = data.agentInfo.pre_agent_lvl; i < data.pre_agent_lvl; i++){
                        sql += ',agent_' + i + ' = ? ';
                        data.agentInfo['agent_' + i] = newAgent;
                        value.push(newAgent);
                    }
                    sql += ',agent_' + data.pre_agent_lvl + ' = ? ';
                    data.agentInfo['agent_' + data.pre_agent_lvl] = account;
                    value.push(account);
                }else {//升级操作 ,从原等级到新等级都改为自己，其他不变。
                    var oldLvl = data.agentInfo.pre_agent_lvl ? data.agentInfo.pre_agent_lvl : Config.MAX_AGENT_LEVEL;//无等级的从最大值开始改；
                    for(var j = oldLvl; j >= data.pre_agent_lvl; j--){
                        sql += ',agent_' + j + ' = ? ';
                        data.agentInfo['agent_' + j] = account;
                        value.push(account);
                    }
                }

                sql += ', pre_agent_lvl = ? WHERE uid = ? AND app_sid = ? ';
                value.push(data.pre_agent_lvl, data.uid,data.app_sid);
                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback(err);
                        console.log('设置前置代理时修改用户代理表失败: ' + err);
                        return;
                    }

                    if(result.affectedRows > 0){
                        callback(null,null);
                        return;
                    }
                    callback('设置前置代理时修改用户代理表失败');

                });
            },

            //2、设置（所有代理是我的）的代理
            function (res,callback) {
                if(!data.agentInfo.pre_agent_lvl || data.agentInfo.pre_agent_lvl == data.pre_agent_lvl){
                    callback(null, null);
                    return;
                }

                var sql = 'UPDATE user_app_new_agent SET agent_1 = ?,agent_2 = ?,agent_3 = ?, agent_4 = ?,agent_5 = ?,agent_6 = ? ';
                data.agentInfo.agent_1 = data.agentInfo.agent_1 ? data.agentInfo.agent_1 : null;
                data.agentInfo.agent_2 = data.agentInfo.agent_2 ? data.agentInfo.agent_2 : null;
                data.agentInfo.agent_3 = data.agentInfo.agent_3 ? data.agentInfo.agent_3 : null;
                data.agentInfo.agent_4 = data.agentInfo.agent_4 ? data.agentInfo.agent_4 : null;
                data.agentInfo.agent_5 = data.agentInfo.agent_5 ? data.agentInfo.agent_5 : null;
                data.agentInfo.agent_6 = data.agentInfo.agent_6 ? data.agentInfo.agent_6 : null;
                var value = [data.agentInfo.agent_1, data.agentInfo.agent_2, data.agentInfo.agent_3,
                    data.agentInfo.agent_4, data.agentInfo.agent_5, data.agentInfo.agent_6, data.app_sid];
                sql += ' WHERE app_sid = ? AND agent_' + (data.agentInfo.pre_agent_lvl) + ' = ' + data.agentInfo.account;
                value.push(data.agentInfo.account);
                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback(err);
                        console.log(err);
                        console.log('设置前置代理时修改用户代理表失败: ' + err);
                        return;
                    }

                    callback(null,null);
                });
            },
            //4、改变一级下线的间接推荐人为自己
            function (res, callback) {
                //查出一级下线
                var sql = 'SELECT account FROM user_app_new_agent WHERE recommender = ? AND app_sid = ? AND account <> ? ';
                var value = [data.agentInfo.account,data.app_sid,data.agentInfo.account];
                connection.query(sql, value, function (err, result) {
                    if (err) {
                        callback('设置前置代理时查询下级用户失败: ' + err);
                        console.log(err);
                        return;
                    }

                    if(!result || result.length <= 0){//没有下级，直接接口返回
                        callback(null,null);
                        return;
                    }

                    var firstLowAccount = [];
                    result.forEach(function (accountInfo) {
                        firstLowAccount.push(accountInfo.account);
                    });
                    var lowResult = result;
                    if (data.agentInfo.pre_agent_lvl){//如果已经是代理的，不用设置一级下线的间接推荐人为自己；
                        callback(null,lowResult);
                        return;
                    }
                    var upSql = 'UPDATE user_app_new_agent set recommender2 = ? WHERE account in ? AND app_sid = ? AND account <> ? ';
                    connection.query(upSql, [data.agentInfo.account, [firstLowAccount], data.app_sid, data.agentInfo.account], function (err, result) {
                        if (err) {
                            callback('设置前置代理时修改用户代理表失败: ' + err);
                            console.log(err);
                            return;
                        }
                        callback(null,lowResult);
                    });
                });
            },

            //5、设置所有下级的区代、市代(如有需要)、省代(如有需要)
            function (lowResult,callback) {

                if(!lowResult){
                    callback(null,'设置成功');
                    return;
                }

                // data.lowAgentAccounts = [];
                data.allLowAcc = {};
                data.allLowAcc[data.account] = 1;
                data.unReachBottomAcc = lowResult;
                data.toUpdateLowAcc = lowResult;
                data.allLowAccSize = 0;
                lowResult.forEach(function (t) { data.allLowAcc[t] = 1; });

                UpdatePreAgentOfLowAcc(data, connection, callback);

            },


        ], function (err, res) {
            if (err) {
                connection.rollback(function () {
                    connection.release();
                    cb(err);
                });
            } else {
                connection.commit(function (e) {
                    if (e) {
                        connection.rollback(function () {
                            connection.release();
                            cb(e);
                        });
                    } else {
                        connection.release();
                        cb(null, res);
                        //设置成功，再后台去设置减少上级的团队人数，不一定成功没关系，减少他这支的
                        deleteTeamNum(data.agentInfo.recommender,data.agentInfo.app_sid,(data.agentInfo.team_num+1));
                    }
                });
            }
        });

    });
}

userAgentDao.saveWxInfo = function(data, cb) {
    if(!data) return cb(new Error(500));
    var sql = 'update users set wx_nick = ?, wx_avatar = ? where wx_unionid = ? ';
    DBFactory.poolQuery(sql, [data.wx_nick, data.wx_avatar, data.wx_unionid],
        function () {
            cb('no collection');
        },
        function (err) {
            if (err) {
                console.log(err);
                cb('保存微信昵称和头像失败！');
                return;
            }
            cb(null, 'success!');
        }
    );
}


//查看用户推荐情况
userAgentDao.getInviteDoneStatus = function (data, cb) {
    if (!data) {
        cb(new Error(500));
    } else {
        DBFactory.getConnection(function (error, connection) {
            if (error) {
                cb(error);
            } else {
                var userInfo = {};
                async.waterfall([
                    //获取所有用户量数据
                    function (callback) {
                        var sql = "select value from config where app_sid = ? and `key` = 'orderReachNum' limit 1";
                        connection.query(sql, [data.app_key], function (err, result) {
                            if (err) return callback(err);
                            userInfo.orderReachNum = (result[0] && result[0].value) ? result[0].value : 0;
                            callback(null,true);
                        });
                    },
                    function (isOK,callback) {
                        var sqlValue = [];
                        var sql = 'select COUNT(b.uid) as totalNum' +
                            ' from user_app_new_agent a inner join user_app_exinfo b on a.app_sid = b.app_sid and a.uid = b.uid' +
                            ' where a.app_sid = ? and a.recommender = ? and a.created_time > ? ';
                        sqlValue.push(data.app_sid,data.account,Config.APR_ACTIVITY_TIME);
                        if (data.hasOwnProperty('invite_done')) {
                            sql += ' and b.invite_done = ? ';
                            sqlValue.push(data.invite_done);
                        }
                        connection.query(sql, sqlValue, function (err, result) {
                            if (err) return callback(err);
                            console.log(result);
                            userInfo.totalNum = (result[0] && result[0].totalNum > 0) ? result[0].totalNum : 0;
                            callback(null, userInfo.totalNum);
                        });
                    },
                    function (totalNum, callback) {
                        if (totalNum == 0) {
                            return callback(null, userInfo);
                        }
                        var sqlValue = [];
                        var sql = 'select b.uid,a.account,IF(ISNULL(ali_user_id) = 1,0,1) aliAuth,IF(ISNULL(tb_user_id) = 1,0,1) tbAuth,order_num,invite_done' +
                            ' from user_app_new_agent a inner join user_app_exinfo b on a.app_sid = b.app_sid and a.uid = b.uid' +
                            ' where a.app_sid = ? and a.recommender = ? and a.created_time > ? ';
                        sqlValue.push(data.app_sid,data.account,Config.APR_ACTIVITY_TIME);
                        if (data.hasOwnProperty('invite_done')) {
                            sql += ' and b.invite_done = ? ';
                            sqlValue.push(data.invite_done);
                        }
                        sql += " order by a.created_time desc";
                        var pageNum = data.pageNum ? data.pageNum : 10;
                        var page = data.page ? data.page : 1;
                        sql += ' limit ' + (page - 1) * pageNum + ',' + pageNum;
                        connection.query(sql, sqlValue, function (err, result) {
                            userInfo.inviteInfo = result;
                            callback(err, userInfo);
                        });
                    },
                ], function (DbErr, userInfo) {
                    connection.release();
                    if (DbErr) {
                        cb(DbErr);
                    } else {
                        cb(null, userInfo);
                    }
                });
            }
        });
    }
}

userAgentDao.setDirector = function (data, cb) {
    if(!data) return cb(new Error(500));
    var c = {
        reduce_money : data.reduce_money,
        lvl: 6,
        app_sid: data.app_sid,
        name: '总监',
        vip_name: data.vip_name ? data.vip_name + '红包抵扣' : '升级总监抵扣'
    };
    console.log('setDirector 扣除最高金额：' + c.reduce_money + ' date : ' + new Date());
    DBFactory.getConnection(function(error, connection) {
        if(error) return cb(error);
        var sql = 'select uid, money from user_app_new_agent where account = ? and app_sid = ? ';
        connection.query(sql, [data.account, data.app_sid], function(err, result) {
            if(err) {
                console.log(data.account + ' ： 设置总监失败！');
                console.log(err);
                cb(err);
                connection.release();
                return;
            } else {
                if(result && result[0] && result[0].money != undefined) {
                    c.original_money = result[0].money;
                    c.uid = result[0].uid;
                    var info = {uid: result[0].uid, app_sid: data.app_sid, pre_agent_lvl: c.lvl};
                    userAgentDao.setPreAgentLvl(info, function(e) {
                        if(e) {
                            connection.release();
                            console.log(e);
                            console.log(data.account + ' ： 设置总监失败！');
                            cb(e);
                            return;
                        } else {
                            if(parseInt(c.reduce_money) > 0) {
                                reduceMoney(c, connection, cb);
                            } else {
                                cb(null, 'success!');
                            }
                        }
                    });
                } else {
                    connection.release();
                    console.log(data.account + ' ： 设置总监失败！');
                    cb('设置总监时发生错误！');
                    return;
                }
            }
        })
    })
}
function reduceMoney(c, connection, cb) {
    var obj = {};
    obj.uid = c.uid;
    obj.app_sid = c.app_sid;
    obj.original_money = c.original_money;
    obj.vip_name = c.vip_name;
    obj.sign = 1;
    if(parseFloat(c.original_money) == 0) {
        connection.release();
        cb(null, 'success!');
        return;
    } else if(parseFloat(c.original_money) >= c.reduce_money) {
        var sql = ' update user_app_new_agent set money = money - ? where uid = ? and app_sid = ? ';
        connection.query(sql, [c.reduce_money, c.uid, c.app_sid], function(err) {
            if(err) {
                console.log(err);
                console.log(c.uid + '  设置'+ c.name +'成功，未扣除金额 ' + c.reduce_money);
                cb(err);
                connection.release();
                return;
            } else {
                obj.c_money = c.reduce_money;
                recordUserMoney(obj, connection, function(err) {
                    if(err) {
                        console.log(c.uid + '  设置 ' + c.name + '成功，扣除金额, 记录日志失败 ');
                    }
                    cb(null, 'success!');
                    connection.release();
                    return;
                })
            }
        });
    } else {
        var sql = 'update user_app_new_agent set money = 0 where uid = ? and app_sid = ? '
        connection.query(sql, [c.uid, c.app_sid], function(err) {
            if(err) {
                console.log(err);
                console.log(c.uid + '  设置' + c.name + '成功，未扣除金额 ' + c.original_money);
                cb(err);
                connection.release();
                return;
            } else {
                obj.c_money = c.original_money;
                recordUserMoney(obj, connection, function(err) {
                    if(err) {
                        console.log(c.uid + '  设置' + c.name + '成功，扣除金额, 记录日志失败 ');
                    }
                    cb(null, 'success!');
                    connection.release();
                    return;
                })
            }
        });
    }
}
function recordUserMoney(info, connection, callback) {
    var arr = {};
    arr.uid = info.uid;
    arr.app_sid = info.app_sid;
    arr.original_money = info.original_money;
    arr.money = info.c_money;
    arr.comment =  info.vip_name;
    arr.sign = info.sign;
    var sql = 'insert into user_money set ? ';
    connection.query(sql, arr, function(err, result) {
        if(err) return callback(err);
        callback(null);
    });
}

userAgentDao.setJihuo = function (data, cb) {
    if(!data) return cb(new Error(500));
    var c = {
        reduce_money : data.reduce_money,
        app_sid: data.app_sid,
        name: '微淘客',
        vip_name: '升级微淘客抵扣'
    };
    console.log('setJihuo 扣除最高金额：' + c.reduce_money + ' date : ' + new Date());
    DBFactory.getConnection(function(error, connection) {
        if(error) return cb(error);
        var sql = 'select count(uid) as count from user_vip where account = ? and app_sid = ? ';
        connection.query(sql, [data.account, data.app_sid], function(err, result) {
            if(err) {
                console.log(data.account + ' ： 设置微淘客失败！');
                console.log(err);
                cb(err);
                connection.release();
                return;
            } else {
                if(result && parseInt(result[0].count) > 0) {
                    connection.release();
                    console.log(data.account + '： 已经是微淘客无需升级！');
                    return cb(null, 'success!')
                } else {
                    var sql = 'select uid, money from user_app_new_agent where account = ? and app_sid = ? ';
                    connection.query(sql, [data.account, data.app_sid], function(e, r) {
                        if(e) {
                            console.log(e);
                            console.log(data.account + ' ： 设置微淘客失败！');
                            connection.release();
                            return cb(e);
                        } else {
                            if(r && r[0] && r[0].money != undefined) {
                                c.original_money = r[0].money;
                                c.uid = r[0].uid;
                                c.account = data.account;
                                addJihuo(c, connection, function(err) {
                                    if(err) {
                                        console.log(data.account + ' ： 设置微淘客失败！');
                                        connection.release();
                                        return cb(e);
                                    } else {
                                        if(parseInt(c.reduce_money) > 0) {
                                            reduceMoney(c, connection, cb);
                                        } else {
                                            cb(null, 'success!');
                                        }
                                    }
                                })
                            } else {
                                connection.release();
                                console.log(data.account + ' ： 设置微淘客失败！');
                                cb('设置微淘客时发生错误！');
                                return;
                            }
                        }
                    });
                }
            }
        })
    });
}
function addJihuo(data, connection, cb) {
    var sql = 'insert into user_vip set ? ';
    var info = {};
    info.account = data.account;
    info.uid = data.uid;
    info.app_sid = data.app_sid;
    info.vip_sid = 'jihuo';
    info.vip_type = 0;
    info.lvl = 0;
    info.created_time = new Date();
    info.end_time = '2030-08-08';
    connection.query(sql, info, function(err) {
        if(err) {
            console.log(err);
            return cb(err);
        } else {
            info.vip_name = '管理员设置wtk会员';
            info.comment = '管理员设置wtk会员';
            info.sign = 0;
            setVipLog(info, connection, cb);
        }
    })
}
function setVipLog(data, connection, callback) {
    var sql = 'insert into vip_log set ? ';
    var info = {};
    info.seller_account = 'srv';
    info.client_account = data.account;
    info.vip_name = data.vip_name;
    info.vip_type = 1;
    info.lvl = 0;
    info.vip_expire = '2030-08-08';
    info.created_date = new Date();
    info.comment = data.comment;
    info.app_sid = data.app_sid;
    info.vip_sid = data.vip_sid;
    info.sign = data.sign;
    info.cost = 0;
    info.duration = 0;
    info.price = 0;
    connection.query(sql, info, function(err) {
        if(err) {
            callback(err);
        } else {
            callback(null, info);
        }
    });
}